CREATE OR REPLACE FUNCTION create_table_of_features(character varying) RETURNS VOID AS '
DECLARE 
  vTableName ALIAS FOR $1;
  cur CURSOR FOR SELECT k.fk_string, MAX(f.ft_int_value), MAX(f.ft_character_value)
        FROM t_feature_key k, t_feature f, t_document d
        WHERE d.doc_lr_id = f.ft_entity_id 
            AND f.ft_key_id = k.fk_id
            AND LOWER(k.fk_string) != ''ticketnumber''
        GROUP BY k.fk_string
        ORDER BY 1;
  curTickets CURSOR FOR SELECT DISTINCT d.doc_url
        FROM t_feature_key k, t_feature f, t_document d
        WHERE d.doc_lr_id = f.ft_entity_id 
            AND f.ft_key_id = k.fk_id
        ORDER BY 1;
  curFeatureValues CURSOR (vfeatName CHARACTER VARYING) FOR SELECT f.ft_int_value, f.ft_character_value, d.doc_url
        FROM t_feature_key k, t_feature f, t_document d
        WHERE d.doc_lr_id = f.ft_entity_id 
            AND f.ft_key_id = k.fk_id
            AND k.fk_string = vfeatName
        ORDER BY 1;

  i INTEGER = 0;
  vfeatureCount INTEGER = 0;
  vfeatureNames CHARACTER VARYING(60)[40];
  vfeatureTypes CHARACTER VARYING(60)[40];
  vfeatureName CHARACTER VARYING(60);
  vcreateTableStmt CHARACTER VARYING(4000);
  vupdateStmt CHARACTER VARYING(4000);
  vfeatureIntValue INTEGER;
  vfeatureCharValue CHARACTER VARYING;
  vticketNumber CHARACTER VARYING;
BEGIN
    IF vTableName IS NULL OR trim(vTableName) = '''' THEN
        RAISE EXCEPTION ''First parameter "vTableName" is mandatory. Example: SELECT create_table_of_features(%);'', quote_literal(''ticketfeatures'');
        EXIT;        
    END IF;
    vcreateTableStmt := ''CREATE TABLE '' || quote_ident(vTableName) || '' (TicketNumber CHARACTER(10) PRIMARY KEY NOT NULL'';
    OPEN cur;
    LOOP
        FETCH cur INTO vfeatureName, vfeatureIntValue, vfeatureCharValue;
        IF  NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;
        vfeatureNames[i] := vfeatureName;
        IF vfeatureIntValue IS NOT NULL THEN
            vfeatureTypes[i] := ''INTEGER'';
        ELSE
            vfeatureTypes[i] := ''CHARACTER VARYING(4000)'';
        END IF;
        vcreateTableStmt := vcreateTableStmt || '', '' || vfeatureName || '' '' || vfeatureTypes[i];
        i := i + 1;
    END LOOP;
    CLOSE cur;
    vcreateTableStmt := vcreateTableStmt || '');'';
    vfeatureCount := i;
    
    IF EXISTS(SELECT tablename FROM pg_tables WHERE tablename = vTableName) THEN
        EXECUTE ''DROP TABLE '' || quote_ident(vTableName) || '';'';
    END IF;
    EXECUTE vcreateTableStmt;
    
    OPEN curTickets;
    LOOP
        FETCH curTickets INTO vticketNumber;
        IF NOT FOUND THEN
            EXIT;  -- exit loop
        END IF;
        vticketNumber := substring(vticketNumber FROM (char_length(vticketNumber) - 13) for 10);
        EXECUTE ''INSERT INTO '' || quote_ident(vTableName) || ''(TicketNumber) VALUES ('' || quote_literal(vticketNumber) || '');'';
        --RAISE NOTICE ''INSERTED TICKET %'', vticketNumber;
    END LOOP;
    CLOSE curTickets;

    i := 0;
    LOOP
        IF i >= vfeatureCount THEN
            EXIT;  -- exit loop
        END IF;
        OPEN curFeatureValues(vfeatureNames[i]);
        LOOP
            FETCH curFeatureValues INTO vfeatureIntValue, vfeatureCharValue, vticketNumber;
            IF  NOT FOUND THEN
                EXIT;  -- exit loop
            END IF;
            IF (vfeatureTypes[i]=''INTEGER'' AND vfeatureIntValue IS NOT NULL)
               OR (vfeatureTypes[i]!=''INTEGER'' AND vfeatureCharValue IS NOT NULL 
                AND vfeatureCharValue != '''') THEN
                
                vticketNumber := substring(vticketNumber from (char_length(vticketNumber) - 13) for 10);
                vupdateStmt := ''UPDATE '' || quote_ident(vTableName) || '' SET '' || vfeatureNames[i] || '' = '';
                
                IF vfeatureTypes[i]=''INTEGER'' THEN
                    vupdateStmt := vupdateStmt || vfeatureIntValue;
                ELSE
                    vupdateStmt := vupdateStmt || quote_literal(vfeatureCharValue);
                END IF;
                vupdateStmt := vupdateStmt || '' WHERE TicketNumber = '' || quote_literal(vticketNumber) || '';'';
                EXECUTE vupdateStmt;
                --RAISE NOTICE ''%'', vupdateStmt;
            END IF;
        END LOOP;
        CLOSE curFeatureValues;
        i := i + 1;
    END LOOP;
END;
' LANGUAGE 'plpgsql';


-- SELECT create_table_of_features('');